# -*- coding: utf-8 -*-

import pymysql

#建立链接
conn = pymysql.connect(
    host='127.0.0.1',port=3306,
    user='root',passwd='123456',
    db='nsd1909',charset='utf8'
)

#创建游标 类似于文件对象,通过文件对象可以对文件读写,通过游标对数据库进行操作
cur = conn.cursor()

#编写sql
# #部门
# mk_dep = '''CREATE TABLE departments(
# dep_id INT PRIMARY KEY,dep_name VARCHAR(20)
# )'''
# #员工
# mk_emp = '''CREATE TABLE employees(
# emp_id INT PRIMARY KEY,emp_name VARCHAR(20),dep_id INT,
# CONSTRAINT fk_depid FOREIGN KEY(dep_id) REFERENCES departments(dep_id)
# )
# '''
# mk_sal = '''CREATE TABLE salary(
# id INT PRIMARY KEY,date DATE ,emp_id INT,basic INT,awards INT,
# CONSTRAINT fk_empid FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
# )
# '''
# cur.execute(mk_dep)
# cur.execute(mk_emp)
# cur.execute(mk_sal)

#确认

#添加部门
sql1 = 'INSERT INTO departments VALUES(%s,%s)'

# cur.execute(sql1,(1,'人事部'))
# cur.executemany(sql1,[(2,'运维部'),(3,'开发部'),(4,'测试部'),(5,'财务部'),(6,'市场部')])
sql2 = 'SELECT * FROM departments ORDER BY dep_Id'
# cur.execute(sql2)
#取一条
# result1 = cur.fetchone()
# print(result1)
# print('*'*50)
# #继续向后取2条
# result2 = cur.fetchmany(2)
# print(result2)
# print('*'*50)
#取所有
# result3 = cur.fetchall()
# print(result3)

sql3 = 'UPDATE departments SET dep_name=%s WHERE dep_name=%s'
#cur.execute(sql3,('人力资源部','人事部'))

sql4 = 'DELETE FROM departments WHERE dep_id=%s'
cur.execute(sql4,(6,))

conn.commit()

#关闭游标 关闭链接

conn.close()
cur.close()
